"""
This file be used to create graph data
"""
import MySQLdb

table_type = "posthistory"
"""
database = "isec2014"
store_table = "from_migration_aug13"
user = "root"
password="123"
port=3306

"""
database = "sangeeta"
store_table = "from_migration_aug13"
user = "sangeetal"
password="sangeetal"
port=3307
#"""


db1= MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
db2= MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
insert_cursor = db1.cursor()
select_cursor = db2.cursor()


def read_table_from_sites(to_site):
    temp_table ="`"+ table_type+"_"+to_site.strip()+"_aug13`"
    query = "select postid,comment from "+temp_table + " where posthistorytypeid = 36"
    #print "query = ", query
    select_cursor.execute(query)
    data =  select_cursor.fetchall()
    newline = list()
    for migrated_site in data:
        #print migrated_site
        newline.append(migrated_site)
        #print "line=", line
    return newline


def parse_and_enter_sites(to_site):
     fromSites=read_table_from_sites(to_site)
     all_from_sites = list()
    # store_table = 
     for post in fromSites:
         postid = (str)(post[0])
         site =post[1]
         site = site.split(" ")[1]
         site = site.split("/")[2]
         index=site.rindex(".")
         site = site[0:index]
         #fromSite = site
         initial =site.strip()
         migrated = to_site.strip()
         insert_str = "insert into "+ store_table+" values( "+postid+",'"+initial+"','"+migrated+"')"
         insert_cursor.execute(insert_str)
         
     db1.commit()
         #print site
     print "processing"

"""==========main==============="""
print "reading"
"""=====Not a comment====="""
file = open("site_names")
for site in file:
    print "current_site=", site
    parse_and_enter_sites(site)


db1.close()
db2.close()

print "creating matrix"
#create_matix()    
 
 
"""    
def create_matrix():
    #get all the 50 sites
    sites_50 = get_all_sites()
    #read from the database
    #create matrix
    #write to csv/excels sheet
    print "hello"

def get_all_sites():
    db1= MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
    get_cursor = db1.cursor()
    select_str1 = "select distinct initial from "+table
    select_str2 = "select distinct migrted from "+table
    
    get_cursor.execute(select_str1)
  
    
    get_cursor.execute(select_str2)
    
    db1.close()
    all_sites_dummy = ["stackoverflo","drupl","meta","tex"]
    return  
   

def read_table_from_sites():    
    f1 = open(file1)
    for line in f1:
        newline = 0
        print "line=", line
    print "reading"    

   
file1="D:\Research\ISEC-2014\data\\QueryResults_more.csv"
sites = list()
#coo

"""     